<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Keuangan_model extends CI_Model {

	function __construct()
	{
		parent::__construct();
    }

	function getViewSpp()
	{
	    $query = "SELECT * FROM `view_spp` GROUP BY ANGKATAN, STATUS_PENERIMAAN, PROGRAMID ORDER BY `ANGKATAN`";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
	}

    function insertNeraca($request_var){ //resigned

        $input_angkatan = $request_var['angkatan'];
        $input_penerimaan = $request_var['penerimaan'];

        $query = $this->db->query("SELECT * FROM spp WHERE `ANGKATAN`='$input_angkatan' AND `STATUS_PENERIMAAN`='$input_penerimaan'");

        $sudah_diisi = $query->num_rows(); //Jika hasilnya nol berarti belum ada neraca angkatan ybs

        if($sudah_diisi == 0){

           $query = "INSERT INTO spp
                     VALUES (
				              NULL,
				              '{$request_var['angkatan']}',
				              {$request_var['penerimaan']},
				              {$request_var['registrasi']}
				     )";
	 	   mysql_query($query);
           return 1;

        }
        else{

           return 0;

        }

     }

    /*
    function updateNeraca($request_var)
	{
        $id=$request_var['id'];

        $query = "UPDATE spp SET
				 `ANGSURAN_1` = {$request_var['registrasi']}
				 WHERE `ID` = $id LIMIT 1";
        $this->db->query($query);

        redirect('siku/inisiasi.html', 'location');

    }
    */

    function deleteNeraca($id)
	{
        $query = "DELETE FROM spp WHERE ID='{$id}'";
        mysql_query($query);

        redirect('siku/inisiasi.html', 'location');

    }

    function insertTagihan($request_var)
    {
        $query_distinct = $this->db->query("SELECT DISTINCT SUBSTRING(NIM,1,2) AS ANGKATAN FROM mahasiswa");

        $i=0;

        if($query_distinct->num_rows() > 0){

           /* Block 1 */
           $result_distinct = $query_distinct->result();

           foreach($result_distinct as $value_distinct){

             if(is_numeric($value_distinct->ANGKATAN))
			 {
			   $angkatan[$i] = $value_distinct->ANGKATAN;
			   $i++;
			 }

           }

           /*Block 2 */
           $query_mhs = $this->db->query("SELECT NIM, STATUS_PENERIMAAN, PROGRAMID FROM mahasiswa WHERE LEVEL=5");
           $query_neraca = $this->db->query("SELECT * FROM neraca WHERE TAHUN = '{$request_var['tahun']}' AND SEMESTER = {$request_var['semester']}");

           if($query_neraca->num_rows() == 0){

             if($query_mhs->num_rows() > 0){

                $result_mhs = $query_mhs->result();

                foreach($result_mhs as $value_mhs){

                  for($loop=0;$loop<$i;$loop++)
                  {
                    if(substr($value_mhs->NIM,0,2) ==  $angkatan[$loop])
				    {
				      $query_tagihan = $this->db->query("SELECT * FROM view_spp WHERE ANGKATAN=$angkatan[$loop] AND STATUS_PENERIMAAN=$value_mhs->STATUS_PENERIMAAN AND PROGRAMID='$value_mhs->PROGRAMID'");

                      $npm = $value_mhs->NIM;
            		  $tahun = $request_var['tahun'];
                      $semester = $request_var['semester'];

                      if($query_tagihan->num_rows() > 0)
                      {
                         $result_tagihan = $query_tagihan->result(); 
                         foreach($result_tagihan as $tgh)
                         {
                            $angsuran = $tgh->NOMINAL;

                            $query_insert_neraca = " INSERT INTO neraca VALUES (NULL,'$npm',$tgh->ID,$tgh->ID_TAGIHAN,'$tahun',$semester,$tgh->NOMINAL,'0','') ";
            				$this->db->query($query_insert_neraca);

                         }
                      }
                    }
                  }

                }

                return 1; // sukses

             }
             else{

                return 3; //hak akses belum diset ke mahasiswa

             }
           }
           else
           {
             return 0; // neraca pada semester dan tahun tsb sudah dikirim
           }

        }
        else{

           return 2; // data mahasiswa tidak ada

        }
    }

    function getViewBiodata($nim)
    {
      $query = "SELECT * FROM view_data_mahasiswa WHERE `NIM` = '$nim'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return FALSE;
    }

    function getViewDetailNeraca($nim)
    {
      $query = "SELECT * FROM view_data_neraca WHERE `NIM` = '$nim' AND `STATUS`!='1'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->result();
        return $data;
      }
      else
        return FALSE;
    }

    function updateDataNeraca($id)
    {
      $query = "UPDATE neraca SET `STATUS`=1 WHERE `ID_NERACA`='$id' ";
      $query = $this->db->query($query);
    }

    function updateStatusMahasiswa($nim,$status)
    {
        $query = "UPDATE mahasiswa SET `STATUS`='$status' WHERE `NIM`='$nim' ";
        $query = $this->db->query($query);
    }

    function getViewTagihanLain()
	{
	    $query = "SELECT * FROM `tagihan_lain`";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
	}

    function updateTagihanLain()
    {
        for($i=1;$i<=3;$i++)
        {
          $id = $this->input->post('id_'.$i);
          $input_nominal = $this->input->post('value_'.$i);
          $query = "UPDATE tagihan_lain SET `NOMINAL`='{$input_nominal}' WHERE `ID`='{$id}' ";
          $query = $this->db->query($query);
        }
    }

    function getTagihanSkripsi($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=1";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getTagihanKkn($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=2";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getTagihanPpl($nim)
    {
      $query = "SELECT * FROM transaksi_tagihan_lain WHERE `NIM` = '$nim' AND `KODE_TAGIHAN`=3";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        return FALSE;
      }
      else
        return TRUE;
    }

    function getNominalTagihan($kode)
    {
      $query = "SELECT * FROM tagihan_lain WHERE `ID` = '$kode'";
      $query = $this->db->query($query);

      if($query->num_rows()>0){
        $data = $query->row();
        return $data;
      }
      else
        return 0;
    }

    function insertTagihanLain($nim,$tagihan)
    {
        $query = "INSERT INTO transaksi_tagihan_lain VALUES (
					'', '{$nim}', $tagihan)";
        $query = $this->db->query($query);
    }

    function getTableTagihan()
	{
	    $query = "SELECT * FROM `tagihan`";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
	}

    function insertSPP($request){

       $this->request_var = $request;

       $query = "INSERT INTO spp
                     VALUES (
				              NULL,
                              '{$this->request_var['prodi']}',
                              '{$this->request_var['Nama']}',
				              '{$this->request_var['angkatan']}',
				              {$this->request_var['penerimaan']},
				              '{$this->request_var['programID']}',
                              {$this->request_var['tagihan']},
                              '{$this->request_var['DefJumlah']}'
				     )";
	    $this->db->query($query);
    }

    function selectSumViewSpp($angkatan,$penerimaan,$programID,$id_tagihan)
	{
	    $query = "SELECT SUM(NOMINAL) AS TOTAL_NOMINAL FROM `view_spp` WHERE ANGKATAN='$angkatan' AND STATUS_PENERIMAAN=$penerimaan AND PROGRAMID='$programID' AND ID_TAGIHAN=$id_tagihan";
        $query = $this->db->query($query);

        return $query->row();
	}

    function selectSppById($idSpp)
	{
	    $query = "SELECT * FROM `view_spp` WHERE ID=$idSpp";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->row();
        else
          $data = NULL;

        return $data;
	}

    function selectSppByTagihan($angkatan,$penerimaan,$programID,$id_tagihan)
	{
	    $query = "SELECT * FROM `view_spp` WHERE ANGKATAN='$angkatan' AND STATUS_PENERIMAAN=$penerimaan AND PROGRAMID='$programID' AND ID_TAGIHAN=$id_tagihan";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
	}

    function deleteSppRelated($idSpp)
	{
	    $data = $this->selectSppById($idSpp);

        if(!$data)
         echo "Permintaan tidak dapat diproses! Mungkin data yang ingin dihapus sudah tidak ada!"; exit();

        $query = "DELETE FROM spp WHERE ANGKATAN = '$data->ANGKATAN' AND PRODI = '$data->KODE_PRODI' AND STATUS_PENERIMAAN = $data->STATUS_PENERIMAAN AND PROGRAMID = '$data->PROGRAMID'";
        $sql = mysql_query($query);

        if($sql)
         redirect('siku/inisiasi.html', 'location');
        else
         echo "Permintaan tidak dapat diproses. Silakan coba lagi!";
    }

    function selectPotonganByNimWhereTahunSemester($nim,$kode_tahun,$kode_semester)
	{
	    $query = "SELECT p.POTONGAN AS POTONGAN, tgh.DESKRIPSI AS DESKRIPSI, p.KETERANGAN AS KETERANGAN, p.ID AS ID
                  FROM (((`potongan` p JOIN tahun_ajaran ta) join semester smt) join tagihan tgh)
                  WHERE
                     p.NIM='$nim' AND p.TAHUN='$kode_tahun' AND p.SEMESTER=$kode_semester AND
                     p.TAHUN=ta.ID AND p.SEMESTER=smt.ID AND p.ID_TAGIHAN=tgh.ID
                 ";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->result();
        else
          $data = NULL;

        return $data;
	}

    function insertPotongan($request){

       $this->request_var = $request;

       $query = "INSERT INTO potongan
                     VALUES (
				              NULL,
                              '{$this->request_var['nim']}',
                              '{$this->request_var['tahun']}',
				              {$this->request_var['semester']},
				              {$this->request_var['DefJumlah']},
				              {$this->request_var['tagihan']},
                              '{$this->request_var['keterangan']}'
				     )";
	    $this->db->query($query);
    }

    function deletePotongan($id)
	{
        $sql = $this->db->query("SELECT NIM FROM potongan WHERE ID='$id'");

        if($sql->num_rows()>0)
        {
          $result = $sql->row();

          $query = "DELETE FROM potongan WHERE ID='{$id}'";
          mysql_query($query);

          redirect('siku/PotonganSpp/'.$result->NIM.'.do', 'location');
        }
        else
        {
          echo "ID yang Anda maksud tidak ada dalam database!";
        }
    }

    function selectPotonganById($id)
	{
	    $query = "SELECT * FROM potongan WHERE ID='$id'";
        $query = $this->db->query($query);

        if($query->num_rows()>0)
          $data = $query->row();
        else
          $data = NULL;

        return $data;
	}

    function updatePotongan($id)
    {
        $query = "UPDATE potongan
                  SET
                    `POTONGAN`='{$this->input->post('DefJumlah')}',
                    `ID_TAGIHAN`={$this->input->post('tagihan')},
                    `KETERANGAN`='{$this->input->post('keterangan')}'
                  WHERE `ID`=$id
                 ";
        $query = $this->db->query($query);
    }
}